package com.hotel.repository;

import com.hotel.config.DatabaseConfig;
import com.hotel.entity.Checkin;

import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class CheckinRepository {
    public CheckinRepository() {}

    public void save(Checkin checkin)
    {
        String sql = "INSERT INTO checkins (guest_id,room_id, checkin_date) VALUES (?,?,?)";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setInt(1, checkin.getGuest_id());
            stmt.setInt(2, checkin.getRoom_id());
            stmt.setTimestamp(3, Timestamp.valueOf(
                    LocalDateTime.ofInstant(Instant.parse(checkin.getCheck_in_time()), ZoneId.systemDefault())));
            stmt.executeUpdate();
            try (ResultSet rs = stmt.getGeneratedKeys()) {
                if (rs.next()) {
                    checkin.setCheckin_id(rs.getInt(1));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("办理入住失败", e);
        }
    }

    public List<Map<String, Object>> findByParam(String name, String id_card, String room_number, String status, String startDate, String endDate) {
        List<Map<String, Object>> checkins = new ArrayList<>();
        String  sql ="SELECT  checkin_id , name , id_card , room_number , room_type , price , phone,checkin_date , checkout_date , total_amount , c.status" +
                " FROM CheckIns c join Guests g on c.guest_id=g.guest_id join Rooms r on c.room_id=r.room_id " +
        "WHERE name LIKE ? AND id_card LIKE ? AND room_number LIKE ? AND c.status Like ? and checkin_date between ? and ? order by c.checkin_id";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%"+name+"%");
            stmt.setString(2, "%"+id_card+"%");
            stmt.setString(3, "%"+room_number+"%");
            stmt.setString(4, "%"+status+"%");
            if(!startDate.isEmpty()) stmt.setTimestamp(5, Timestamp.valueOf(startDate+" 00:00:00"));
            else stmt.setTimestamp(5, Timestamp.valueOf("1001-01-01 00:00:00"));
            if(!endDate.isEmpty()) stmt.setTimestamp(6, Timestamp.valueOf(endDate+" 23:59:59"));
            else stmt.setTimestamp(6, Timestamp.valueOf(LocalDateTime.now()));
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> checkin = mapResultSetToCheckin(rs);
                    checkins.add(checkin);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询入住信息失败", e);
        }
       return checkins;
    }


    public List<Map<String, Object>> findAll() {
        List<Map<String, Object>> checkins = new ArrayList<>();
        String  sql ="SELECT c.checkin_id , name , id_card , room_number , room_type , r.price ,phone, checkin_date , checkout_date , total_amount , c.status" +
                " FROM CheckIns c join Guests g on c.guest_id=g.guest_id join Rooms r on c.room_id=r.room_id order by c.checkin_id";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> checkin = mapResultSetToCheckin(rs);
                    checkins.add(checkin);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询入住信息失败", e);
        }
        return checkins;
    }
    //checkout目标，修改状态，退房时间，计算总价。修改房间状态为空闲。
    public void checkout(int id,String checkout_time) {
        String sql = "UPDATE CheckIns SET status='已退房' , checkout_date=? WHERE checkin_id=?";
        String sql1 = "UPDATE CheckIns c JOIN Rooms r ON c.room_id = r.room_id " +
                "SET c.total_amount = c.total_amount + (r.price * DATEDIFF(c.checkout_date, c.checkin_date)) " +
                "WHERE c.checkin_id = ?";
        String sql2 = "UPDATE Rooms SET status='空闲' WHERE room_id= (SELECT room_id FROM CheckIns WHERE checkin_id=?)";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql);
             PreparedStatement stmt1 = conn.prepareStatement(sql1);
             PreparedStatement stmt2 = conn.prepareStatement(sql2)) {
            stmt.setTimestamp(1, Timestamp.valueOf(checkout_time));
            stmt.setInt(2, id);
            stmt.executeUpdate();

            stmt1.setInt(1, id);
            stmt1.executeUpdate();

            stmt2.setInt(1, id);
            stmt2.executeUpdate();

             } catch (SQLException e) {
            throw new RuntimeException("办理退房失败", e);
        }
    }

    public void update(Checkin checkin) {
     String sql="UPDATE CheckIns SET room_id=?, checkin_date=? WHERE checkin_id=?";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, checkin.getRoom_id());
            stmt.setTimestamp(2, Timestamp.valueOf(checkin.getCheck_in_time()));
            stmt.setInt(3, checkin.getCheckin_id());
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新入住信息失败", e);
        }

    }

    public Optional<Map<String, Object>> findById(int checkin_id) {
        String  sql ="SELECT * FROM CheckIns join Guests on CheckIns.guest_id=Guests.guest_id join Rooms on CheckIns.room_id=Rooms.room_id WHERE checkin_id= ?";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, checkin_id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapResultSetToCheckin(rs));
                }
            }
            return Optional.empty();
        } catch (SQLException e) {
            throw new RuntimeException("查询入住信息失败", e);
        }
    }
    public List<Map<String, Object>>  getActive() {
        List<Map<String, Object>> checkins = new ArrayList<>();
        String  sql ="SELECT checkin_id,guest_id,guest_name,id_card,room_number,room_type,room_price,checkin_date,checkout_date,total_amount,c.status " +
                "FROM CheckIns c join GUESTS g on c.guest_id=g.guest_id join Rooms r on c.room_id=r.room_id " +
                "WHERE checkins.status = '入住中'";
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> checkin = mapResultSetToCheckin(rs);
                    checkins.add(checkin);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询入住信息失败", e);
        }
        return checkins;
    }

    public List<Map<String, Object>> findCheckinsByIdCard(String id_card) {
        String sql = "select checkin_id,room_number,room_type,price,checkin_date,checkout_date,total_amount,c.status" +
                " from checkins c join guests g on c.guest_id = g.guest_id " +
                "join rooms r on c.room_id = r.room_id where id_card = ?";
        List<Map<String, Object>> checkins = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(DatabaseConfig.URL, DatabaseConfig.USER, DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, id_card);
            try (ResultSet rs = stmt.executeQuery()) {
                while(rs.next()) {
                    Map<String, Object> checkin = new HashMap<>();
                    checkin.put("checkin_id", rs.getInt("checkin_id"));
                    checkin.put("room_number", rs.getString("room_number"));
                    checkin.put("room_type", rs.getString("room_type"));
                    checkin.put("room_price", rs.getBigDecimal("price"));
                    checkin.put("checkin_date", rs.getTimestamp("checkin_date"));
                    if(rs.getTimestamp("checkout_date")!=null) checkin.put("checkout_date", rs.getTimestamp("checkout_date"));
                    checkin.put("total_amount", rs.getString("total_amount"));
                    checkin.put("status", rs.getString("status"));
                    checkins.add(checkin);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询入住记录失败", e);
        }
        return checkins;
    }

    private Map<String, Object> mapResultSetToCheckin(ResultSet rs) throws SQLException {
        Map<String, Object> checkin = new HashMap<>();
        checkin.put("checkin_id", rs.getInt("checkin_id"));
        checkin.put("guest_name", rs.getString("name"));
        checkin.put("id_card", rs.getString("id_card"));
        checkin.put("phone", rs.getString("phone"));
        checkin.put("room_number", rs.getString("room_number"));
        checkin.put("room_type",rs.getString("room_type"));
        checkin.put("room_price",rs.getBigDecimal("price"));
        checkin.put("checkin_date", rs.getTimestamp("checkin_date"));
        if(rs.getTimestamp("checkout_date")!=null)
            checkin.put("checkout_date", rs.getTimestamp("checkout_date"));
        else checkin.put("checkout_date", null);
        checkin.put("total_amount", rs.getDouble("total_amount"));
        checkin.put("status", rs.getString("status"));
        return checkin;
    }




}
